import os
import re
from datetime import datetime
from operator import add as fn_add
from functools import reduce
import mail
import utils
from openpyxl import Workbook

os.chdir(os.path.dirname(os.path.abspath(__file__)))
# 过滤SQL文件，并生成key:value为{序号:文件名}的字典
opt_file_dict = {str(opt): file.name for opt, file in
                 enumerate(filter(lambda f: str.endswith(f.name, ".sql"), os.scandir()))}
# 如果程序目录没有SQL文件，则直接退出
if not opt_file_dict:
    print("请将要导出的sql文件复制到程序目录后，再次运行该程序!")
    exit(0)

opt_sql_list_dict = {}
user_select_opts = ""
while user_select_opts.lower() == "":
    print("将导出以下SQL文件查询的结果表格")
    # 打印所有"序号+文件名"格式的SQL文件选项
    for opt, file_name in opt_file_dict.items():
        print(opt, "、", file_name)
        # 读取SQL文件的多个SQL，并将SQL列表赋值给相应序号
        with open(opt_file_dict[opt], encoding="utf8") as f:
            opt_sql_list_dict[opt] = filter(lambda s: s != "", f.read().rstrip(";").split(";\n"))

    user_select_opts = reduce(fn_add, opt_file_dict.keys())

exp_files = []

for opt in user_select_opts:
    sql_list = opt_sql_list_dict[opt]
    file_name: str = opt_file_dict[opt].replace(".sql", ".xlsx")
    # 新建一个工作薄对象
    wb = Workbook()
    wb.remove(wb.active)
    for sql in sql_list:
        # 如果SQL文件有注释，则用注释重命名sheet
        sheet_name_diy = re.match(r'\s*([-]{2,}|[#]+)\s+(\w+)', sql)
        sheet_name = sheet_name_diy.groups()[-1] if sheet_name_diy else utils.get_before_day(0)
        row_count, headers, rows = utils.get_data(sql)
        if row_count > 0:
            print("-------------------------------------")
            utils.get_excel(rows, headers, sheet_name, wb)
            print(f"写入【{sheet_name}】完毕！")
        else:
            print(f"{opt}、【{file_name}】-（{sheet_name}无数据）")
    # 保存excel文件
    wb.save(file_name)
    exp_files.append(file_name)

subject, content = "业务报表：" + utils.get_before_day(1), "统计表见附件，请注意查收！"
files = [os.getcwd() + r"/" + f for f in exp_files]
msg = mail.create_email(subject, content, files)
